package com.foreknow.elm.dao.impl;

import com.foreknow.elm.dao.DeliveryAddressDao;
import com.foreknow.elm.po.DeliveryAddress;
import com.foreknow.elm.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DeliveryAddressDaoImpl implements DeliveryAddressDao {
    private Connection con;
    private PreparedStatement pst;
    private ResultSet rs;
    private int result = 0;
    @Override
    public List<DeliveryAddress> listDeliveryAddressByUserId(String userId) throws SQLException{
        List<DeliveryAddress> list = new ArrayList<>();
        String sql = "select * from deliveryaddress where userId=?";
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setString(1,userId);
            rs = pst.executeQuery();
            while(rs.next()){
               DeliveryAddress deliveryaddress = new DeliveryAddress();
               deliveryaddress.setDaId(rs.getInt("daId"));
               deliveryaddress.setContactName(rs.getString("contactName"));
               deliveryaddress.setContactSex(rs.getInt("contactSex"));
               deliveryaddress.setContactTel(rs.getString("contactTel"));
               deliveryaddress.setAddress(rs.getString("address"));
               deliveryaddress.setUserId(rs.getString("userId"));
               list.add(deliveryaddress);
            }
        } finally {
            DBUtil.close(pst);
        }
        return list;
    }

    @Override
    public DeliveryAddress getDeliveryAddressById(Integer daId) throws SQLException{
        DeliveryAddress deliveryaddress =  new DeliveryAddress();;
        String sql = "SELECT * from deliveryaddress where daId=?";
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setInt(1,daId);
            rs = pst.executeQuery();
            while(rs.next()){
                deliveryaddress.setDaId(rs.getInt("daId"));
                deliveryaddress.setContactName(rs.getString("contactName"));
                deliveryaddress.setContactSex(rs.getInt("contactSex"));
                deliveryaddress.setContactTel(rs.getString("contactTel"));
                deliveryaddress.setAddress(rs.getString("address"));
                deliveryaddress.setUserId(rs.getString("userId"));;
            }
        } finally {
            DBUtil.close(pst);
        }
        return deliveryaddress;
    }

    @Override
    public int saveDeliveryAddress(String contactName, Integer contactSex, String contactTel, String address, String userId) throws SQLException {
        String sql = "insert into deliveryaddress(contactName,contactSex,contactTel,address,userId) values(?,?,?,?,?)";
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setString(1,contactName);
            pst.setInt(2,contactSex);
            pst.setString(3,contactTel);
            pst.setString(4,address);
            pst.setString(5,userId);
            result = pst.executeUpdate();
        }finally {
            DBUtil.close(pst);
        }
        return result;
    }

    @Override
    public int updateDeliveryAddress(Integer daId, String contactName, Integer contactSex, String contactTel, String address, String userId) throws SQLException {
        String sql = "update deliveryaddress set contactName=?,contactSex=?,contactTel=?,address=?,userId=? where daId=?";
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setString(1,contactName);
            pst.setInt(2,contactSex);
            pst.setString(3,contactTel);
            pst.setString(4,address);
            pst.setString(5,userId);
            pst.setInt(6,daId);
            result = pst.executeUpdate();
        }finally {
            DBUtil.close(pst);
        }
        return result;
    }

    @Override
    public int removeDeliveryAddress(Integer daId) throws SQLException{
        String sql = "DELETE from deliveryaddress where daId=?";
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setInt(1,daId);
            result = pst.executeUpdate();
        }finally {
            DBUtil.close(pst);
        }
        return result;
    }
}
